package bbs;

import java.io.InputStream;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.Statement;
import java.util.ArrayList;

import util.DB;

public class FileDAO {

    private static File makeFile(ResultSet rs) throws Exception {
        File obj = new File();
        obj.setId(rs.getInt("Id"));
        obj.setUserId(rs.getInt("userId"));
        obj.setArticleId(rs.getInt("articleId"));
        obj.setFileName(rs.getString("fileName"));
        obj.setFileSize(rs.getInt("fileSize"));
        obj.setFileTime(rs.getTimestamp("fileTime"));
        return obj;
    }

    public static ArrayList<File> selectByArticleId(int articleId) throws Exception {
        String sql = "SELECT [id], userId, articleId, fileName, fileSize, fileTime FROM bbs.[File] " +
                     "WHERE articleId=?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, articleId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<File> list = new ArrayList<File>();
                while (rs.next())
                    list.add(makeFile(rs));
                return list;
            }
        }
    }

    public static ArrayList<File> selectOrphanByUserId(int userId) throws Exception {
        String sql = "SELECT [id], userId, articleId, fileName, fileSize, fileTime FROM bbs.[File] " +
                     "WHERE articleId IS NULL and userId = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, userId);
            try (ResultSet rs = stmt.executeQuery()) {
                ArrayList<File> list = new ArrayList<File>();
                while (rs.next())
                    list.add(makeFile(rs));
                return list;
            }
        }
    }

    public static int insert(int articleId, int userId, String fileName, int fileSize, InputStream inputStream) throws Exception {
        String sql = "INSERT INTO bbs.[File] ([articleId], [userId], [fileName], [fileSize], [fileTime], [data]) VALUES (?, ?, ?, ?, GETDATE(), ?)";
        try (Connection conn = DB.getConnection();
             PreparedStatement stmt = conn.prepareStatement(sql, Statement.RETURN_GENERATED_KEYS)) {
            if (articleId > 0) stmt.setInt(1, articleId);
            else stmt.setNull(1, java.sql.Types.INTEGER);
            stmt.setInt(2, userId);
            stmt.setString(3, fileName);
            stmt.setInt(4, fileSize);
            stmt.setBinaryStream(5, inputStream);
            stmt.executeUpdate();
            try (ResultSet generatedKeys = stmt.getGeneratedKeys()) {
                return generatedKeys.next() ? generatedKeys.getInt(1) : 0;
            }
        }
    }

    public static void delete(int id) throws Exception {
        String sql = "DELETE bbs.[File] WHERE [id] = ?";
        try (Connection con = DB.getConnection();
             PreparedStatement stmt = con.prepareStatement(sql)) {
            stmt.setInt(1, id);
            stmt.executeUpdate();
        }
    }

}
